import pandas as pd
import pymysql
from common.sqlOperate import *

host = '192.168.3.44'
#host = 'localhost'
port = 3306
#user = 'root'
#password = 'root1234'
user = 'test123'
password = 'test1234'
#db='gwza0204'
db = 'gwza0422'
#db = 'uwb44'
#db = 'gwza_refactory_local'
name = "5_10第二次围栏40"

ob = sqlOperate(host, port, user, password, db)
ob.sqlConnect()
id = ob.reqRuleId(name)
#id = 712
num,devices = ob.reqHelmetVlt(id)
arr1 =[]
for deviceid in devices:
    arr1.append(deviceid[0])

print(arr1)

#arr1 = ['GW4D39', 'GW50ED', 'GW5014', 'GW5120', 'GW4949', 'GW1D75', 'GW53A5', 'GW505F', 'GW4F08']

df = pd.read_excel('./config/data.xls',sheet_name='410', header=0)
# 获取第二列数据
#second_column = df.iloc[:, 1] # iloc[row_indexer, column_indexer]，这里的1表示第二列
# print('________________________')
# first_column = df.iloc[0:5,1]  #获取第二列的前5行数据
# print(first_column)
# print('________________________')

values = df.values
print(len(values))
arr2 =[]
for v in values:
    arr2.append(v[0])
print(arr2)

# 将两个数组转换为集合
set_a = set(arr1)
set_b = set(arr2)

# 找到两个集合的差集
result = set_b - set_a
#result=", ".join(list(map(str, result)))
#print(type(result), result)
# 将结果转换回列表
result_list = list(result)
result2 = set_a - set_b
result_list2 = list(result2)

print("data表减去违规数的差值：")
print(result_list)
print("违规数减去data表：")
print(result_list2)

